﻿using Common.DBUtility;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoCreatTables.DAL
{
    public class DTablesManager
    {
        /// <summary>
        /// 获取数据库按月分表的表名
        /// </summary>
        /// <param name="trans">事物</param>
        /// <param name="conn">链接</param>
        /// <param name="databasename">数据库名</param>
        /// <param name="tablePostfix">分表后缀</param>
        /// <returns>按月分表的</returns>
        public List<string> GetTablesFromDatabase(IDbTransaction trans, IDbConnection conn, string databasename, string tablePostfix)
        {
            List<string> datatables = new List<string>();
            string sql = @"select table_name,table_schema from information_schema.tables where table_schema =@DatabaseName and table_type='base table' and table_name like @table_name; ";
            MySqlParameters parameters = new MySqlParameters();
            parameters.Add(new MySqlParameter() { ParameterName = "@DatabaseName", MySqlDbType = MySqlDbType.VarChar, Value = databasename });
            parameters.Add(new MySqlParameter() { ParameterName = "@table_name", MySqlDbType = MySqlDbType.VarChar, Value = string.Format("%{0}", tablePostfix) });
            using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    string datetable = reader["table_name"] == DBNull.Value ? string.Empty : reader["table_name"].ToString();
                    datatables.Add(datetable);
                }
            }

            return datatables;
        }

        /// <summary>
        /// 获取数据库名
        /// </summary>
        /// <param name="trans">事物</param>
        /// <param name="conn">链接</param>
        /// <returns>按月分表的</returns>
        public List<string> GetDataBseName(IDbTransaction trans, IDbConnection conn)
        {
            List<string> datatables = new List<string>();
            string sql = @"SHOW DATABASES;; ";
            using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sql, null))
            {
                while (reader.Read())
                {
                    string datetable = reader["Database"] == DBNull.Value ? string.Empty : reader["Database"].ToString();
                    List<string> systemdatabase = new List<string>() { "information_schema", "mysql", "performance_schema" };
                    if (!systemdatabase.Contains(datetable.ToLower()))
                    {
                        datatables.Add(datetable);
                    }
                }
            }

            return datatables;
        }

        /// <summary>
        /// 复制表
        /// </summary>
        /// <param name="trans">事物</param>
        /// <param name="conn">链接</param>
        /// <param name="databasename">数据库名</param>
        /// <param name="oldtable">被复制的表名</param>
        /// <param name="newtable">新表名</param>
        /// <returns></returns>
        public int CreatTableFromDatabase(IDbTransaction trans, IDbConnection conn, string databasename, string oldtable, string newtable)
        {
            string sql = string.Format(@"USE {0}; CREATE TABLE {1} LIKE {2}; ", databasename, newtable, oldtable);
            return Common.DBUtility.MySqlHelper.ExecuteSql(trans, conn, sql, null);
        }
    }
}
